package com.allen.service;

import com.allen.Utils.JdbcUtils;
import com.allen.bean.Chart;
import com.allen.bean.Charts;
import com.allen.bean.Goods;
import net.sf.json.JSONObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChartService {
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public List<Goods> getAllGoods() {

        List<Goods> goodses = new ArrayList<>();
        String sql = "SELECT gid,gname FROM goods;";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGid(resultSet.getInt("gid"));
                goods.setGname(resultSet.getString("gname"));
                goodses.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return goodses;
    }

    public String chartJson(int gid) {

        Charts charts = new Charts();
        String sql1 = "SELECT date,SUM(IFNULL(num,0)) FROM instore WHERE gid=? GROUP BY date;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1,gid);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Chart chart1 = new Chart();
                chart1.setDate(resultSet.getString(1));
                chart1.setNum(resultSet.getInt(2));
                charts.getInchart().add(chart1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        String sql2 = "SELECT date,SUM(IFNULL(num,0)) FROM outstore WHERE gid=? GROUP BY date;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1,gid);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Chart chart2 = new Chart();
                chart2.setDate(resultSet.getString(1));
                chart2.setNum(resultSet.getInt(2));
                charts.getOutchart().add(chart2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        String chartsjson = JSONObject.fromBean(charts).toString();
        return chartsjson;
    }
}
